package com.parkreport.report.service;

import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.xml.rpc.ServiceException;

import org.jfree.data.time.Year;

import com.common.jdbc.DB4ObjUtil;
import com.common.report.service.ReportService;
import com.common.report.vo.ReportSumDataVO;
import com.common.report.vo.ReportTitleVO;
import com.common.util.StringUtil;
import com.parkreport.vo.ReportParamVO;

public class YearToYearPersonReportService extends ReportService {

	
	
	@Override
	public List findDatas(HttpServletRequest req) throws ServiceException {
		year = req.getParameter("year");
		if(StringUtil.isBlank(year)){
			year = new Year().toString();
			req.setAttribute("year", year);
		}
		StringBuilder sql = new StringBuilder("");
		sql.append("select pa.AREA_NAME as ments, substr(lpr.RQSJ,1,7) as titles, sum(cc.PRE_PERSON) as counts ");
		sql.append(" from LPR_RECORD lpr,CAR_AREA ca,QUERY_DMB qd,PARK_DMB pd,PARK_AREA pa,CAR_COLOR cc");
		sql.append(" where 1=1");
		sql.append(" and pd.TYPE='1'");
		sql.append(" and substr(lpr.PLATENO,1,2) =ca.carNum");
		sql.append(" and lpr.NID = qd.NID ");
		sql.append(" and qd.NID = pd.NID");
		sql.append(" and pd.PARK_AREA_ID=pa.ID");
		sql.append(" and cc.COLOR_NAME=lpr.COLOR");
		sql.append(" and lpr.RQSJ like '"+year+"%'");
		sql.append(" group by substr(lpr.RQSJ,1,7), pa.AREA_NAME");
		
		log.info("sql=========="+sql);
		List<ReportSumDataVO> list = DB4ObjUtil.execute4Obj(new ReportSumDataVO(), sql.toString());
		String preYear = Year.parseYear(year).previous().toString();
		StringBuilder sql1 = new StringBuilder("");
		sql1.append("select pa.AREA_NAME as ments, substr(lpr.RQSJ,1,7) as titles, sum(cc.PRE_PERSON) as counts ");
		sql1.append(" from LPR_RECORD lpr,CAR_AREA ca,QUERY_DMB qd,PARK_DMB pd,PARK_AREA pa,CAR_COLOR cc");
		sql1.append(" where 1=1");
		sql1.append(" and pd.TYPE='1'");
		sql1.append(" and substr(lpr.PLATENO,1,2) =ca.carNum");
		sql1.append(" and lpr.NID = qd.NID ");
		sql1.append(" and qd.NID = pd.NID");
		sql1.append(" and pd.PARK_AREA_ID=pa.ID");
		sql1.append(" and cc.COLOR_NAME=lpr.COLOR");
		sql1.append(" and lpr.RQSJ like '"+preYear+"%'");
		sql1.append(" group by substr(lpr.RQSJ,1,7), pa.AREA_NAME");
		
		log.info("sql=========="+sql);
		List<ReportSumDataVO> list1 = DB4ObjUtil.execute4Obj(new ReportSumDataVO(), sql1.toString());
		list.addAll(list1);
		return list;
	}
	
	
	
	@Override
	public List<ReportTitleVO> findTitles(HttpServletRequest req) throws ServiceException {
		List<ReportTitleVO> list = new ArrayList<ReportTitleVO>();
		
		if(StringUtil.isBlank(year)){
			year = new Year().toString();
		}
		Year pyear = Year.parseYear(year);
		String preYear = pyear.previous().toString();
		List<ReportTitleVO> yearList = new ArrayList<ReportTitleVO>();
		yearList.add(new ReportTitleVO(preYear,preYear) );
		yearList.add(new ReportTitleVO(year,year) );
		
		list.add(new ReportTitleVO("1月","01",yearList) );
		list.add(new ReportTitleVO("2月","02",yearList) );
		list.add(new ReportTitleVO("3月","03",yearList) );
		list.add(new ReportTitleVO("4月","04",yearList) );
		list.add(new ReportTitleVO("5月","05",yearList) );
		list.add(new ReportTitleVO("6月","06",yearList) );
		list.add(new ReportTitleVO("7月","07",yearList) );
		list.add(new ReportTitleVO("8月","08",yearList) );
		list.add(new ReportTitleVO("9月","09",yearList) );
		list.add(new ReportTitleVO("10月","10",yearList) );
		list.add(new ReportTitleVO("11月","11",yearList) );
		list.add(new ReportTitleVO("12月","12",yearList) );
		return list;
	}
	
	@Override
	public List<ReportTitleVO> findMents(HttpServletRequest req) throws ServiceException {
		StringBuilder sql = new StringBuilder("");
		sql.append("select AREA_NAME as code,AREA_NAME as name from park_area where PID<>'0'");
		List<ReportTitleVO> list = DB4ObjUtil.execute4Obj(new ReportTitleVO(), sql.toString());
		
		//list.add(new ReportTitleVO("合   计","合计"));
		return list;
	}
}
